package com.perselab.recipe.dao.impl;

import java.io.Serializable;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashSet;
import java.util.List;
import java.util.Set;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.hibernate.Criteria;
import org.hibernate.HibernateException;
import org.hibernate.Query;
import org.hibernate.Session;
import org.hibernate.criterion.Projections;
import org.springframework.orm.ObjectRetrievalFailureException;
import org.springframework.orm.hibernate3.HibernateCallback;
import org.springframework.orm.hibernate3.support.HibernateDaoSupport;

import com.perselab.recipe.bean.Filter;
import com.perselab.recipe.dao.GenericDao;
import com.perselab.recipe.model.Model;

/**
 * This class serves as the Base class for all other DAOs - namely to hold
 * common CRUD methods that they might all use. You should only need to extend
 * this class when your require custom CRUD logic.
 *
 * <p>To register this class in your Spring context file, use the following XML.
 * <pre>
 *      &lt;bean id="fooDao" class="net.globaltrade.core.dao.hibernate.GenericDaoHibernate"&gt;
 *          &lt;constructor-arg value="net.globaltrade.core.model.Foo"/&gt;
 *          &lt;property name="sessionFactory" ref="sessionFactory"/&gt;
 *      &lt;/bean&gt;
 * </pre>
 *
 * @param <T> a type variable
 * @param <PK> the primary key for that type
 */
public class GenericDaoHibernate<T extends Model<PK>, PK extends Serializable> extends HibernateDaoSupport implements GenericDao<T, PK> {
    /**
     * Log variable for all child classes. Uses LogFactory.getLog(getClass()) from Commons Logging
     */
    protected final Log log = LogFactory.getLog(getClass());
    private Class<T> persistentClass;

    /**
     * Constructor that takes in a class to see which type of entity to persist
     * @param persistentClass the class type you'd like to persist
     */
    public GenericDaoHibernate(final Class<T> persistentClass) {
        this.persistentClass = persistentClass;
    }

    /**
     * {@inheritDoc}
     */
    @SuppressWarnings("unchecked")
    public List<T> getAll() {
        return super.getHibernateTemplate().loadAll(this.persistentClass);
    }

    /**
     * {@inheritDoc}
     */
    @SuppressWarnings("unchecked")
    public T get(PK id) {
        T entity = (T) super.getHibernateTemplate().get(this.persistentClass, id);

        if (entity == null) {
            log.warn("Uh oh, '" + this.persistentClass + "' object with id '" + id + "' not found...");
            throw new ObjectRetrievalFailureException(this.persistentClass, id);
        }

        return entity;
    }

    /**
     * {@inheritDoc}
     */
    @SuppressWarnings("unchecked")
    public boolean exists(PK id) {
        T entity = (T) super.getHibernateTemplate().get(this.persistentClass, id);
        return entity != null;
    }

    /**
     * {@inheritDoc}
     */
    @SuppressWarnings("unchecked")
    public T save(T object) {
        return (T) super.getHibernateTemplate().merge(object);
    }

    /**
     * {@inheritDoc}
     */
    public void remove(PK id) {
        super.getHibernateTemplate().delete(this.get(id));
    }
    
    /**
     * Count entity with filder
     * @param filter
     * @return
     */
    public int getPersistentsCountWithFilter(final PersistentFilter filter) {
        Integer count = (Integer) getHibernateTemplate().execute(new HibernateCallback() {
            public Object doInHibernate(Session session)
                    throws HibernateException, SQLException {
            	Criteria criteria = session.createCriteria(persistentClass);
                criteria = filter.execute(criteria);
                criteria.setProjection(Projections.distinct(Projections.property("id")));
                
                List ids = criteria.list();
                return ids.size();
            }
        });

        return count.intValue();
    }

    /**
     * query sorted entities with filter 
     * @param filter
     * @param sort
     * @param rowStart
     * @param rowEnd
     * @return
     */
    @SuppressWarnings("unchecked")
    public List<T> getPersistentsWithFilterAndSort(final PersistentFilter filter, final PersistentSort sort, final int rowStart, final int rowEnd) {        
		List<T> persistents = (List<T>) getHibernateTemplate().execute(new HibernateCallback() {
            public Object doInHibernate(Session session)
                    throws HibernateException, SQLException {
                Criteria criteria = session.createCriteria(persistentClass);
                criteria = filter.execute(criteria);
                criteria = sort.execute(criteria);
                criteria.setFirstResult(rowStart)
                		.setMaxResults(rowEnd - rowStart)
                		.setProjection(Projections.distinct(Projections.property("id")));
                
                List ids = criteria.list();
                
                if(ids.isEmpty()){
                	return new ArrayList<T>();
                }
                
                StringBuffer queryString = new StringBuffer();
                queryString.append("select alias from " + persistentClass.getName() + " alias where id in (:ids) group by alias");
                final Query query = getSession().createQuery(queryString.toString());
                query.setParameterList("ids", ids);
                
                return query.list();
            }
        });

        return persistents;
    }
    
	/**
	 * {@inheritDoc}
	 */	
	@SuppressWarnings("unchecked")
	public List<T> getFiltered(final List<Filter> filters,
			final String sortBy, final String sortOrd, final int start,
			final int maxResults) throws IllegalArgumentException {

		// get sub-queries
		final Set<String> subQueries = new HashSet<String>();
		for (Filter filter : filters) {
			if (filter.getProperty().indexOf(".") > 0) {
				subQueries.add(filter.getProperty().split("\\.")[0]);
			}
		}
		
		return (List<T>) getHibernateTemplate().execute(
				new HibernateCallback() {
					public Object doInHibernate(Session session)
							throws HibernateException {
						
						
						String className = persistentClass.getName();
						if (className.lastIndexOf('.') > 0) {
							className = className.substring(className.lastIndexOf('.')+1); 
						}
						
						String mainAlias = className.substring(0,1).toLowerCase() + className.substring(1);						
						
						StringBuffer queryBuffer = new StringBuffer();

						// SELECT CLAUSE
						queryBuffer.append(" select ").append(mainAlias);
						// FROM CLAUSE
						queryBuffer.append(" from  ")
							.append(persistentClass.getName())
							.append(" ")
							.append(mainAlias)
							.append(" ");
						
						for (String subQuery : subQueries) {
							queryBuffer.append(" join ")
									.append(mainAlias)
									.append(".")
									.append(subQuery).append(" as ")
									.append(subQuery);
						}

						// WHERE CLAUSE
						if (filters.size() > 0) {
							queryBuffer.append(" where ");
						}

						boolean first = true;
						String property;
						for (Filter filter : filters) {
							if (!first)
								queryBuffer.append(" and ");
							
							property = filter.getProperty();
							if (property.indexOf(".") < 0) {
								property = mainAlias.concat(".").concat(property);
							}
							
							if (filter.getOperation().equals("=") && filter.getValue() instanceof Date) {
								// special condition for date and EQUALS operator
								queryBuffer
								.append(property)
								.append(" between ")
								.append(" :")
								.append(filter.getProperty()
										.replaceAll("\\.", ""))
								.append("0 AND :")
								.append(filter.getProperty()
										.replaceAll("\\.", ""))
								.append("1 ");								
								
							} else if ("BETWEEN".equals(filter.getOperation())) {
								queryBuffer
										.append(property)
										.append(" between ")
										.append(" :")
										.append(filter.getProperty()
												.replaceAll("\\.", ""))
										.append("0 AND :")
										.append(filter.getProperty()
												.replaceAll("\\.", ""))
										.append("1 ");
							} else if ("CONTAINS".equals(filter.getOperation())) {
								queryBuffer
										.append(property)
										.append(" like ")
										.append(" :")
										.append(filter.getProperty()
												.replaceAll("\\.", ""))
										.append(" ");
							} else if ("LIKE".equals(filter.getOperation())) {
								queryBuffer
										.append(property)
										.append(" like ")
										.append(" :")
										.append(filter.getProperty()
												.replaceAll("\\.", ""))
										.append(" ");								
							} else if ("IS NULL".equals(filter.getOperation())) {
								queryBuffer
										.append(property)
										.append(" IS NULL ");
							} else if ("IS NOT NULL".equals(filter.getOperation())) {
								queryBuffer
										.append(property)
										.append(" IS NOT NULL ");	
							} else if ("IS NOT EMPTY".equals(filter.getOperation())) {
								queryBuffer
										.append(property)
										.append(" != '' ");			
							} else if ("IS EMPTY".equals(filter.getOperation())) {
								queryBuffer
										.append(property)
										.append(" = '' ");									
							} else {
								String operation = filter.getOperation().toString();
								operation = operation.replace("&lt;", "<").replace("&gt;", ">");
								queryBuffer
										.append(property)
										.append(" ")
										.append(operation)
										.append(" :")
										.append(filter.getProperty()
												.replaceAll("\\.", ""))
										.append(" ");
							}

							first = false;
						}

						// set the order
						queryBuffer.append(" order by ");
						String sort = sortBy;
						if (!sortBy.contains("\\.")) {
							queryBuffer.append(mainAlias).append(".").append(sortBy);
						} else {
							queryBuffer.append(sort);
						}
						queryBuffer.append(" ").append(sortOrd);

						Query query = session.createQuery(queryBuffer
								.toString());

						
						// set the parameters
						for (Filter filter : filters) {
							if (filter.getOperation().equals("=") && filter.getValue() instanceof Date) {
								Calendar c = Calendar.getInstance();
								c.setTime((Date)filter.getValue());
								c.add(Calendar.DATE, 1);
								
								query.setParameter(filter.getProperty()
										.replaceAll("\\.", "").concat("0"),
										(Date)filter.getValue());
								query.setParameter(filter.getProperty()
										.replaceAll("\\.", "").concat("1"),
										c.getTime());

							} else if ("BETWEEN".equals(filter.getOperation())) {
								List<Object> values = ((List<Object>) filter.getValue());
								query.setParameter(filter.getProperty()
										.replaceAll("\\.", "").concat("0"),
										values.get(0));
								query.setParameter(filter.getProperty()
										.replaceAll("\\.", "").concat("1"),
										values.get(1));
							} else if ("CONTAINS".equals(filter.getOperation())) {
								StringBuffer value = new StringBuffer();
								value.append("%").append(filter.getValue())
										.append("%");
								query.setParameter(filter.getProperty()
										.replaceAll("\\.", ""), value
										.toString());
							} else if ("LIKE".equals(filter.getOperation())) {
								StringBuffer value = new StringBuffer();
								value.append(filter.getValue());
								query.setParameter(filter.getProperty()
										.replaceAll("\\.", ""), value
										.toString());
							} else if ("IS NOT NULL".equals(filter.getOperation())) {
							} else if ("IS NULL".equals(filter.getOperation())) {
							} else if ("IS NOT EMPTY".equals(filter.getOperation())) {
							} else if ("IS EMPTY".equals(filter.getOperation())) {								
							} else {
								query.setParameter(filter.getProperty().replaceAll("\\.", ""), 
										filter.getValue());
							}
						}
						
						//System.err.println("OH " + query.getQueryString());
						
						if (maxResults > 0) {
							query.setMaxResults(maxResults);
						}

						if (start > 0) {
							query.setFirstResult(start);
						}

						return query.list();

					}
				});
	}
	
	
	/**
	 * {@inheritDoc}
	 */	
	@SuppressWarnings("unchecked")
	public Long getFilteredCount(final List<Filter> filters) throws IllegalArgumentException {

		// get sub-queries
		final Set<String> subQueries = new HashSet<String>();
		for (Filter filter : filters) {
			if (filter.getProperty().indexOf(".") > 0) {
				subQueries.add(filter.getProperty().split("\\.")[0]);
			}
		}
		
		return (Long) getHibernateTemplate().execute(
				new HibernateCallback() {
					public Object doInHibernate(Session session)
							throws HibernateException {
						
						
						String className = persistentClass.getName();
						if (className.lastIndexOf('.') > 0) {
							className = className.substring(className.lastIndexOf('.')+1); 
						}
						
						String mainAlias = className.substring(0,1).toLowerCase() + className.substring(1);						
						
						StringBuffer queryBuffer = new StringBuffer();

						// SELECT CLAUSE
						queryBuffer.append(" select count(*) ");
						// FROM CLAUSE
						queryBuffer.append(" from  ")
							.append(persistentClass.getName())
							.append(" ")
							.append(mainAlias)
							.append(" ");
						
						for (String subQuery : subQueries) {
							queryBuffer.append(" join ")
									.append(mainAlias)
									.append(".")
									.append(subQuery).append(" as ")
									.append(subQuery);
						}

						// WHERE CLAUSE
						if (filters.size() > 0) {
							queryBuffer.append(" where ");
						}

						boolean first = true;
						String property;
						for (Filter filter : filters) {
							if (!first)
								queryBuffer.append(" and ");
							
							property = filter.getProperty();
							if (property.indexOf(".") < 0) {
								property = mainAlias.concat(".").concat(property);
							}
							
							if (filter.getOperation().equals("=") && filter.getValue() instanceof Date) {
								// special condition for date and EQUALS operator
								queryBuffer
								.append(property)
								.append(" between ")
								.append(" :")
								.append(filter.getProperty()
										.replaceAll("\\.", ""))
								.append("0 AND :")
								.append(filter.getProperty()
										.replaceAll("\\.", ""))
								.append("1 ");								
								
							} else if ("BETWEEN".equals(filter.getOperation())) {
								queryBuffer
										.append(property)
										.append(" between ")
										.append(" :")
										.append(filter.getProperty()
												.replaceAll("\\.", ""))
										.append("0 AND :")
										.append(filter.getProperty()
												.replaceAll("\\.", ""))
										.append("1 ");
							} else if ("CONTAINS".equals(filter.getOperation())) {
								queryBuffer
										.append(property)
										.append(" like ")
										.append(" :")
										.append(filter.getProperty()
												.replaceAll("\\.", ""))
										.append(" ");
							} else if ("LIKE".equals(filter.getOperation())) {
								queryBuffer
										.append(property)
										.append(" like ")
										.append(" :")
										.append(filter.getProperty()
												.replaceAll("\\.", ""))
										.append(" ");								
							} else if ("IS NULL".equals(filter.getOperation())) {
								queryBuffer
										.append(property)
										.append(" IS NULL ");
							} else if ("IS NOT NULL".equals(filter.getOperation())) {
								queryBuffer
										.append(property)
										.append(" IS NOT NULL ");	
							} else if ("IS NOT EMPTY".equals(filter.getOperation())) {
								queryBuffer
										.append(property)
										.append(" != '' ");			
							} else if ("IS EMPTY".equals(filter.getOperation())) {
								queryBuffer
										.append(property)
										.append(" = '' ");									
							} else {
								String operation = filter.getOperation().toString();
								operation = operation.replace("&lt;", "<").replace("&gt;", ">");								
								queryBuffer
										.append(property)
										.append(" ")
										.append(operation)
										.append(" :")
										.append(filter.getProperty()
												.replaceAll("\\.", ""))
										.append(" ");
							}

							first = false;
						}

						//System.err.println("OH " + queryBuffer.toString());
						
						Query query = session.createQuery(queryBuffer
								.toString());

						// set the parameters
						for (Filter filter : filters) {
							if (filter.getOperation().equals("=") && filter.getValue() instanceof Date) {
								Calendar c = Calendar.getInstance();
								c.setTime((Date)filter.getValue());
								c.add(Calendar.DATE, 1);
								
								query.setParameter(filter.getProperty()
										.replaceAll("\\.", "").concat("0"),
										(Date)filter.getValue());
								query.setParameter(filter.getProperty()
										.replaceAll("\\.", "").concat("1"),
										c.getTime());

							} else if ("BETWEEN".equals(filter.getOperation())) {
								List<Object> values = ((List<Object>) filter.getValue());
								query.setParameter(filter.getProperty()
										.replaceAll("\\.", "").concat("0"),
										values.get(0));
								query.setParameter(filter.getProperty()
										.replaceAll("\\.", "").concat("1"),
										values.get(1));
							} else if ("CONTAINS".equals(filter.getOperation())) {
								StringBuffer value = new StringBuffer();
								value.append("%").append(filter.getValue())
										.append("%");
								query.setParameter(filter.getProperty()
										.replaceAll("\\.", ""), value
										.toString());
							} else if ("LIKE".equals(filter.getOperation())) {
								StringBuffer value = new StringBuffer();
								value.append(filter.getValue());
								query.setParameter(filter.getProperty()
										.replaceAll("\\.", ""), value
										.toString());
							} else if ("IS NOT NULL".equals(filter.getOperation())) {
							} else if ("IS NULL".equals(filter.getOperation())) {
							} else if ("IS NOT EMPTY".equals(filter.getOperation())) {
							} else if ("IS EMPTY".equals(filter.getOperation())) {								
							} else {
								query.setParameter(filter.getProperty()
										.replaceAll("\\.", ""), filter
										.getValue());
							}
						}

						return query.uniqueResult();

					}
				});
	}    
	
}
